
clear all

// This file creates a county-district-level dataset for the upper and lower houses of the state legislature using precinct-level elections data for 2017 spending (typically 2014 election year)

// Precinct-level election results from Harvard Dataverse (Ansolobehere et al.)
// Accessed here: https://projects.iq.harvard.edu/eda/data
// saved in a folder Data/input/Precinct Election results/2014
// Download yearly data by state (merged data only has 2010 geo identifiers and more recent elections)

// The data includes only the election years used in the final study
// State-specific corrections are noted in the Online Appendix

// To merge county FIPS codes to county names, download the County FIPS matching tool from Carl Klarner: 
// Accessed here: https://dataverse.harvard.edu/dataset.xhtml?persistentId=doi:10.7910/DVN/OSLU4G
// saved as "$InputPath/countyfipstool.dta"

tempfile csldu
gen censusyear=2020
save `csldu', emptyok

tempfile csldl
save `csldl', emptyok

tempfile st
save `st', emptyok

 
// ALABAMA

local i 14
//state elections for 2010 only in AL
import delim "$InputPath/Precinct election results/2014/al_20`i'_heda.csv", clear 
replace state="Alabama"
rename (sd ld ) (sldu sldl )

replace county="De Kalb" if county=="DeKalb" 

merge m:1 state county using "$InputPath/countyfipstool.dta"
keep if _m==3
drop _m

***state specific***
drop if precinct=="ABSENTEE" | precinct=="PROVISIONAL" //reported at county levels
********************

rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape


foreach v of varlist g20`i'_sth* g20`i'_sts* {
	replace `v'="" if `v'=="NA"
		destring `v', replace 
}

save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl=="NA" & nsd>1


collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty county sldl)
 
append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu

drop if sldu=="NA" & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state county fcounty sldu)

append using `csldu'
save `csldu', replace


// COLORADO -- added in 2014 only

local i 14
import delim "$InputPath/Precinct election results/2014/co_20`i'_heda.csv", clear 
replace state="Colorado"
rename (sd ld) (sldu sldl)

merge m:1 state county using "$InputPath/countyfipstool.dta"
keep if _m==3
drop _m

rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape


foreach v of varlist g20`i'_sth* g20`i'_sts* {
	tostring `v', replace
	replace `v'="" if `v'=="NA"
		destring `v', replace 
}

***state specific***
tostring ld1 sd1, replace
********************

save `st', replace


reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl=="NA" & nsd>1

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu=="NA" & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace


// DELAWARE -- has 2014 election data, but only half of precincts reporting. Not used in paper.

// FLORIDA
local i 14 

import delim "$InputPath/Precinct election results/2014/FL_20`i'_heda.csv", clear 
replace state="Florida"
rename (sd ld) (sldu sldl)


***state specific***
do "$CodePath/elections_csld_edits/districts_FL.do" //add county
********************

rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape

foreach v of varlist g20`i'_sth* g20`i'_sts* {
	tostring `v', replace
	replace `v'="" if `v'=="NA"
		destring `v', replace 
}

save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl=="NA" & nsd>1

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu=="NA" & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace


//GEORGIA 
local i 14

import delim "$InputPath/Precinct election results/2014/ga_20`i'_heda.csv", clear 
replace state="Georgia"
rename (sd ld) (sldu sldl)

*** state-specific ***
replace county="Decatur" if county=="decateur"
replace county="Ben Hill" if county=="ben_hill"
replace county="Jeff Davis" if county=="jeff_davis"
replace county="Habersham" if county=="haberhsam"

tostring sldu sldl, replace
************************

merge m:1 state county using "$InputPath/countyfipstool.dta"
keep if _m==3
drop _m



rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape

foreach v of varlist g20`i'_sth* g20`i'_sts* {
	tostring `v', replace
	replace `v'="" if `v'=="NA"
		destring `v', replace 
}

save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl=="NA" & nsd>1

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu=="NA" & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace


// IOWA
local i 14

import delim "$InputPath/Precinct election results/2014/ia_20`i'_heda.csv", clear 
replace state="Iowa"
rename (sd ld) (sldu sldl)


tostring sldu sldl, replace

*** State-specific
replace county="Black Hawk" if county=="BlackHawk"
replace county="Buena Vista" if county=="BuenaVista"
replace county="Cerro Gordo" if county=="CerroGordo"
replace county="Des Moines" if county=="DesMoines"
replace county="Palo Alto" if county=="PaloAlto"
replace county="Van Buren" if county=="VanBuren"

***

merge m:1 state county using "$InputPath/countyfipstool.dta"
keep if _m==3
drop _m



rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape

foreach v of varlist g20`i'_sth* g20`i'_sts* {
	tostring `v', replace
	replace `v'="" if `v'=="NA"
		destring `v', replace 
}

save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl=="NA" & nsd>1

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu=="NA" & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace



// ILLINOIS

local i 14 

import delim "$InputPath/Precinct election results/2014/il_20`i'_heda.csv", clear 
drop state
gen state="Illinois"
rename (sd ld) (sldu sldl)


tostring sldu sldl, replace

*** State-specific

replace county=upper(county)
replace county="KANE" if county=="CITY OF AURORA-KANE"
replace county="Kendall" if county=="CITY OF AURORA-KENDALL"
replace county="Will" if county=="CITY OF AURORA-WILL"

replace county="MCLEAN" if county=="CITY OF BLOOMINGTON-MCLEAN"
replace county="Cook" if county=="CITY OF CHICAGO-COOK"
replace county="Vermilion" if county=="CITY OF DANVILLE-VERMILION"

replace county="St Clair" if county=="CITY OF EAST ST. LOUIS-ST. CLAIR"
replace county="Knox" if county=="CITY OF GALESBURG-KNOX"

replace county="Peoria" if county=="CITY OF PEORIA-PEORIA"
replace county="Winnebago" if county=="CITY OF ROCKFORD-WINNEBAGO"
replace county="DE KALB" if county=="DeKALB"

replace county="DE WITT" if county=="DeWITT"
replace county="DU PAGE" if county=="DuPAGE"
replace county="Jo Daviess" if county=="JoDAVIESS"

***

merge m:1 state county using "$InputPath/countyfipstool.dta"
keep if _m==3
drop _m



rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape

foreach v of varlist g20`i'_sth* g20`i'_sts* {
	tostring `v', replace
	replace `v'="" if `v'=="NA"
		destring `v', replace 
}

save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl=="NA" & nsd>1

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu=="NA" & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace


// KANSAS --only U.S. senate elections in 2014

// KENTUCKY --not included in 2014 release

// LOUISIANA --only federal 

// MASSACHUSETTS -- does not merge with Census redistricting post-2010 report, not used in final dataset

//MARYLAND
local i 14 

import delim "$InputPath/Precinct election results/2014/md_20`i'_heda.csv", clear 
drop state
gen state="Maryland"
rename (sd ld) (sldu sldl)


tostring sldu sldl, replace

** state specific **

//county codes comes from raw data on SOS website: https://www.elections.maryland.gov/elections/2014/index.html
gen countyn=county
drop county
gen county=""
replace county="Allegany" if countyn==1
replace county="Anne Arundel" if countyn==2
replace county="Baltimore City" if countyn==3

replace county="Baltimore County" if countyn==4
replace county="Calvert" if countyn==5
replace county="Caroline" if countyn==6

replace county="Carroll" if countyn==7
replace county="Cecil" if countyn==8
replace county="Charles" if countyn==9

replace county="Dorchester" if countyn==10
replace county="Frederick" if countyn==11
replace county="Garrett" if countyn==12

replace county="Harford" if countyn==13
replace county="Howard" if countyn==14
replace county="Kent" if countyn==15

replace county="Montgomery" if countyn==16
replace county="Prince Georges" if countyn==17
replace county="Queen Annes" if countyn==18

replace county="St Marys" if countyn==19
replace county="Somerset" if countyn==20
replace county="Talbot" if countyn==21

replace county="Washington" if countyn==22
replace county="Wicomico" if countyn==23
replace county="Worcester" if countyn==24

tostring elec_district precinct, replace
replace precinct="0"+precinct if length(precinct)==1
replace precinct=elec_district+precinct+sldl

gen censusyear=2020
gen sldlname=sldl 
gen fstate=24
do "$CodePath/elections_csld_edits/elections_add_sldlcodes.do" //replace string districts with code

drop sldlname fstate
**

merge m:1 state county using "$InputPath/countyfipstool.dta"
keep if _m==3
drop _m



rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape

foreach v of varlist g20`i'_sth* g20`i'_sts* {
	tostring `v', replace
	replace `v'="" if `v'=="NA"
		destring `v', replace 
}

save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl=="NA" & nsd>1

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu=="NA" & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace



//MAINE

local i 14

import delim "$InputPath/Precinct election results/2014/me_20`i'_heda.csv", clear 
drop state
gen state="Maine"
rename (sd ld) (sldu sldl)


tostring sldu sldl, replace

** state specific **
drop if county=="Emergency" | county=="State"
replace county="Androscoggin" if county=="AND"
replace county="Aroostook" if county=="ARO"
replace county="Cumberland" if county=="CUM"
replace county="Franklin" if county=="FRA"
replace county="Hancock" if county=="HAN"
replace county="Kennebec" if county=="KEN"
replace county="Knox" if county=="KNO"
replace county="Lincoln" if county=="LIN"
replace county="Oxford" if county=="OXF"
replace county="Penobscot" if county=="PEN"
replace county="Piscataquis" if county=="PIS"
replace county="Sagadahoc" if county=="SAG"
replace county="Somerset" if county=="SOM"
replace county="Waldo" if county=="WAL"
replace county="Washington" if county=="WAS"
replace county="York" if county=="YOR"
**

merge m:1 state county using "$InputPath/countyfipstool.dta"
keep if _m==3
drop _m



rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape

foreach v of varlist g20`i'_sth* g20`i'_sts* {
	tostring `v', replace
	replace `v'="" if `v'=="NA"
		destring `v', replace 
}

save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl=="NA" & nsd>1

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu=="NA" & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace


// MICHIGAN 
local i 14

import delim "$InputPath/Precinct election results/2014/mi_20`i'_heda.csv", clear 
drop state
gen state="Michigan"
rename (sd ld) (sldu sldl)


tostring sldu sldl, replace

** state specific **
replace county="Grand Traverse" if county=="gd. traverse"
tostring precinct, replace
tostring ward, replace
replace precinct=city+precinct+ward
**

merge m:1 state county using "$InputPath/countyfipstool.dta"
keep if _m==3
drop _m



rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape

foreach v of varlist g20`i'_sth* g20`i'_sts* {
	tostring `v', replace
	replace `v'="" if `v'=="NA"
		destring `v', replace 
}

save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl=="NA" & nsd>1

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu=="NA" & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace


// MINNESOTA --only lower house
local i 14

import delim "$InputPath/Precinct election results/2014/mn_20`i'_heda.csv", clear 
drop state
gen state="Minnesota"
rename (sd ld) (sldu sldl)


tostring sldu sldl, replace

*** state specific ****

merge m:1 state county using "$InputPath/countyfipstool.dta"
keep if _m==3
drop _m



rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv ) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 ) //prepare for reshape

foreach v of varlist g20`i'_sth*  {
	tostring `v', replace
	replace `v'="" if `v'=="NA"
		destring `v', replace 
}

save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl=="NA" & nsd>1

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace


// MISSOURI 
local i 14

import delim "$InputPath/Precinct election results/2014/mo_20`i'_heda.csv", clear 
drop state
gen state="Missouri"
rename (sd ld) (sldu sldl)


tostring sldu sldl, replace

** state specific **
replace county="Jackson" if county=="Kansas City" //https://www.kceb.org/elections/maps/
replace county="MCDONALD" if county=="McDonald"
drop if county=="OVERSEAS"
**

merge m:1 state county using "$InputPath/countyfipstool.dta"
keep if _m==3
drop _m



rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape

foreach v of varlist g20`i'_sth* g20`i'_sts* {
	tostring `v', replace
	replace `v'="" if `v'=="NA"
		destring `v', replace 
}

save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl=="NA" & nsd>1

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu=="NA" & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace


// MONTANA 

local i 14 

import delim "$InputPath/Precinct election results/2014/mt_20`i'_heda.csv", clear 
drop state
gen state="Montana"
rename (sd ld) (sldu sldl)


tostring sldu sldl, replace

** state specific **
replace county="Lewis And Clark" if county=="Lewis & Clark"
replace county="MCCONE" if county=="McCone"
**

merge m:1 state county using "$InputPath/countyfipstool.dta"
keep if _m==3
drop _m



rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape

foreach v of varlist g20`i'_sth* g20`i'_sts* {
	tostring `v', replace
	replace `v'="" if `v'=="NA"
		destring `v', replace 
}

save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl=="NA" & nsd>1

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu=="NA" & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace



// NEW JERSEY --not included in 2014

// NORTH CAROLINA
local i 14

import delim "$InputPath/Precinct election results/2014/nc_20`i'_heda.csv", clear 
drop state
gen state="North Carolina"
rename (sd ld) (sldu sldl)


tostring sldl sldu, replace

** state specific ****

merge m:1 state county using "$InputPath/countyfipstool.dta"
keep if _m==3
drop _m



rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape

foreach v of varlist g20`i'_sth* g20`i'_sts* {
	tostring `v', replace
	replace `v'="" if `v'=="NA"
		destring `v', replace 
}

save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl=="NA" & nsd>1


*** state specific ***
destring sldl , replace
tostring  sldl, replace
**********************

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu=="NA" & nsd>1

**** state specific ****
destring  sldu, replace
tostring sldu , replace
************************

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace


//NORTH DAKOTA
local i 14 

import delim "$InputPath/Precinct election results/2014/nd_20`i'_heda.csv", clear 
drop state
gen state="North Dakota"
rename (sd ld) (sldu sldl)


tostring sldu sldl, replace

** state specific **
replace county=subinstr(county,"_"," ",.)
replace county=subinstr(county,".nd","",.)
replace county=subinstr(county,",nd","",.)
replace county=upper(county)

drop if sldu=="NA" & sldl=="NA"
**

merge m:1 state county using "$InputPath/countyfipstool.dta"
keep if _m==3
drop _m



rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape

foreach v of varlist g20`i'_sth* g20`i'_sts* {
	tostring `v', replace
	replace `v'="" if `v'=="NA"
		destring `v', replace 
}

save `st', replace

*** state specific ***
drop if ld1=="NA"
**********************

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl=="NA" & nsd>1

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

*** state specific ****
drop if sd1=="NA"
***********************

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu=="NA" & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace


//NEBRASKA --one house only (labeled as upper in data)

local i 14

import delim "$InputPath/Precinct election results/2014/ne_20`i'_heda.csv", clear 
drop state
gen state="Nebraska"
rename (sd) (sldu )


tostring sldu , replace

** state specific **
replace county="Red Willow" if county=="Willow"
replace county="Scotts Bluff" if county=="Bluff"
replace county="MCPHERSON" if county=="McPherson"
replace county="Keya Paha" if county=="Paha"
replace county="Box Butte" if county=="Butte"
**

merge m:1 state county using "$InputPath/countyfipstool.dta"
keep if _m==3
drop _m



rename (sldu g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape

foreach v of varlist g20`i'_sts* {
	tostring `v', replace
	replace `v'="" if `v'=="NA"
		destring `v', replace 
}


reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu=="NA" & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace

//NEW HAMPSHIRE -- does not merge with Census redistricting post-2010 report, not used in final dataset

//NEW MEXICO --lower house only
local i 14


import delim "$InputPath/Precinct election results/2014/nm_20`i'_heda.csv", clear 
drop state
gen state="New Mexico"
rename ( ld) ( sldl)


tostring  sldl, replace

** state specific **
replace county="Dona Ana" if county=="DoÃ±a Ana"
replace county="MCKINLEY" if county=="McKinley"
********************

merge m:1 state county using "$InputPath/countyfipstool.dta"
keep if _m==3
drop _m


rename ( sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv ) (ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 ) //prepare for reshape

foreach v of varlist g20`i'_sth*  {
	tostring `v', replace
	replace `v'="" if `v'=="NA"
		destring `v', replace 
}

save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl=="NA" & nsd>1

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace


//NEVADA
local i 14

import delim "$InputPath/Precinct election results/2014/nv_20`i'_heda.csv", clear 
drop state
gen state="Nevada"
rename (sd ld) (sldu sldl)


tostring sldu sldl, replace

*** state specific ******
replace county=subinstr(county,"_","",.)
replace county="Carson City" if county=="carsoncity"
replace county="Washoe" if county=="washoenew"
*************************

merge m:1 state county using "$InputPath/countyfipstool.dta"
keep if _m==3
drop _m


rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape

foreach v of varlist g20`i'_sth* g20`i'_sts* {
	tostring `v', replace
	replace `v'="" if `v'=="NA"
		destring `v', replace 
}

save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl=="NA" & nsd>1

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu=="NA" & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace


// OHIO  
local i 14

import delim "$InputPath/Precinct election results/2014/oh_20`i'_heda.csv", clear 
drop state
gen state="Ohio"
rename (sd ld) (sldu sldl)



*** state specific ********
destring sldu sldl, replace
tostring sldu sldl, replace
****************************

merge m:1 state county using "$InputPath/countyfipstool.dta"
keep if _m==3
drop _m


rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape

foreach v of varlist g20`i'_sth* g20`i'_sts* {
	tostring `v', replace
	replace `v'="" if `v'=="NA"
		destring `v', replace 
}

save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl=="NA" & nsd>1

** state specific ********
destring  sldl, replace
tostring  sldl, replace
**************************

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu=="NA" & nsd>1

** state specific **
destring sldu , replace
tostring sldu , replace

**

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace

// OKLAHOMA -- most precincts have missing district information, not used in final dataset

// OREGON -- no election data in 2014

//PENNSYLVANIA
local i 14 

import delim "$InputPath/Precinct election results/2014/pa_20`i'_heda.csv", clear 
drop state
gen state="Pennsylvania"
rename (sd ld) (sldu sldl)


tostring sldu sldl, replace

*** state specific ***
gen fcounty=.
do "$CodePath/elections_csld_edits/districts_PA_2014.do" //add fips county codes
**********************

rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape

foreach v of varlist g20`i'_sth* g20`i'_sts* {
	tostring `v', replace
	replace `v'="" if `v'=="NA"
		destring `v', replace 
}

save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl=="NA" & nsd>1

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu=="NA" & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace


// SOUTH CAROLINA
local i 14

import delim "$InputPath/Precinct election results/2014/sc_20`i'_heda.csv", clear 
drop state
gen state="South Carolina"
rename (sd ld) (sldu sldl)


tostring sldu sldl, replace

** state specific **
replace county=upper(county)
replace county="MARLBORO" if county=="MALBORO"
**

merge m:1 state county using "$InputPath/countyfipstool.dta"
keep if _m==3
drop _m


rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape

foreach v of varlist g20`i'_sth* g20`i'_sts* {
	tostring `v', replace
	replace `v'="" if `v'=="NA"
		destring `v', replace 
}

save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl=="NA" & nsd>1

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu=="NA" & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace


// SOUTH DAKOTA
local i 14

import delim "$InputPath/Precinct election results/2014/sd_20`i'_heda.csv", clear 
drop state
gen state="South Dakota"
rename (sd ld) (sldu sldl)


tostring sldu sldl, replace

** state specific ****************
replace county=subinstr(county,"_"," ",.)
replace county=upper(county)
replace county="WALWORTH" if county=="WALWORTH "
replace sldl="" if sldl=="NA" 
replace sldu="" if sldu=="NA" 
//drop if sldl=="NA" & sldu=="NA"
drop if precinct=="Absentee Precinct"
**********************************

merge m:1 state county using "$InputPath/countyfipstool.dta"
keep if _m==3
drop _m

** state specific ** -- most sldl are off by 1, confirmed by merging witih census redistricting and at state legislature website: https://mylrc.sdlegislature.gov/api/Documents/120778.pdf
replace sldl="26" if sldl=="26A"
replace sldl="26.5" if sldl=="26B"
replace sldl="28" if sldl=="28A"
replace sldl="28.5" if sldl=="28B"
destring sldl sldu, replace

foreach s in sldl sldu {
replace `s'=`s'-1 
replace `s'=`s'+1 if fcounty==11 | fcounty==13 | fcounty==15 | fcounty==17 | fcounty==19 | fcounty==25 | fcounty==29 | fcounty==31 | fcounty==37 | fcounty==39 | fcounty==41 | fcounty==51 | fcounty==57 | fcounty==63 | fcounty==76 | fcounty==79 | fcounty==83 | fcounty==85 | fcounty==91 | fcounty==95 | fcounty==97 | fcounty==99 | fcounty==101 | fcounty==103 | fcounty==105 | fcounty==111 | fcounty==115 | fcounty==121 | fcounty==137
}

tostring sldl sldu, replace
******************************


rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape

foreach v of varlist g20`i'_sth* g20`i'_sts* {
	tostring `v', replace
	replace `v'="" if `v'=="NA"
		destring `v', replace 
}

save `st', replace


*** state specific*******
drop if ld1=="."

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl=="NA" & nsd>1

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

*** state specific*****
drop if sd1=="."

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu=="NA" & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace


// TENNESSEE 
local i 14 

import delim "$InputPath/Precinct election results/2014/tn_20`i'_heda.csv", clear 
drop state
gen state="Tennessee"
rename (sd ld) (sldu sldl)


tostring sldu sldl, replace

** state specific **
drop if precinct=="Absentee" | precinct=="Provisional"
replace county=upper(county)
**

merge m:1 state county using "$InputPath/countyfipstool.dta"
keep if _m==3
drop _m


rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape

foreach v of varlist g20`i'_sth* g20`i'_sts* {
	tostring `v', replace
	replace `v'="" if `v'=="NA"
		destring `v', replace 
}

save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl=="NA" & nsd>1

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu=="NA" & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace



// TEXAS 
local i 12 //use 2012 instead of 2014 due to bi-annual legislature meetings (see online appendix notes)

import delim "~/Dropbox/Data/Precinct election results/tx_20`i'.tab", clear 
drop state
gen state="Texas"
rename (sd ld) (sldu sldl)


tostring sldu sldl, replace

** state specific ******
replace county=upper(county)
********************************

merge m:1 state county using "$InputPath/countyfipstool.dta"
keep if _m==3
drop _m


rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape

foreach v of varlist g20`i'_sth* g20`i'_sts* {
	tostring `v', replace
	replace `v'="" if `v'=="NA"
		destring `v', replace 
}

** state specific **
tostring ld2, replace
tostring sd2, replace
**

save `st', replace


reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county vtd) j(nsd) //TX specific
rename ld sldl
drop if sldl=="." // upper house


collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county vtd) j(nsd) //TX specific
rename sd sldu
drop if sldu=="." // lower house

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace


// VIRGINIA --upper house only
local i 14

import delim "$InputPath/Precinct election results/2014/va_20`i'_heda.csv", clear 
drop state
gen state="Virginia"
rename (sd ) (sldu )


tostring sldu , replace

** state specific *****

replace county=upper(county)
replace county="FAIRFAX" if county=="FAIRFAX "
drop if precinct=="Ab - Central Absentee Precinct" | precinct=="Provisional"
************************

merge m:1 state county using "$InputPath/countyfipstool.dta"
keep if _m==3
drop _m


rename (sldu  g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1  g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape

foreach v of varlist  g20`i'_sts* {
	tostring `v', replace
	replace `v'="" if `v'=="NA"
		destring `v', replace 
}

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu=="NA" & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace



// VERMONT 
local i 14

import delim "$InputPath/Precinct election results/2014/vt_20`i'_heda.csv", clear 
drop state
gen state="Vermont"
rename (sd ld) (sldu sldl)


tostring sldu sldl, replace

** state specific ***
drop if precinct=="Absentee" | precinct=="Provisional"

rename county town

replace town=subinstr(town," ","",.) 

replace town=subinstr(town,"City","",.)
replace town=subinstr(town,"Town","",.)
replace town=subinstr(town,"Mount","Mount ",.)
replace town=subinstr(town,"East","East ",.)
replace town=subinstr(town,"South","South ",.)
replace town=subinstr(town,"Saint","St. ",.)
replace town="New Haven" if town=="NewHaven"
replace town="Fair Haven" if town=="FairHaven"
replace town="Grand Isle" if town=="GrandIsle"
replace town="Isle La Motte" if town=="IsleLaMotte"
replace town="Hyde Park" if town=="HydePark"
replace town="East Montpelier" if town=="Montpelier"
replace town="West Rutland" if town=="WestRutland"
replace town="West Windsor" if town=="WestWindsor"
replace town="West Fairlee" if town=="WestFairlee"
replace town="West Haven" if town=="WestHaven"
replace town="Middletown Springs" if town=="MiddletownSprings"
replace town="North Hero" if town=="NorthHero"

replace town="Windham" if town=="shend"

gen county=""
do "$CodePath/elections_csld_edits/districts_VT.do" //add county name to town name

gen censusyear=2020

replace sldl= strtrim(sldl)
do "$CodePath/elections_csld_edits/precinct_sldlcodes_VT.do" //replace string districts with code

drop precinct
gen precinct=_n
replace county="Chittenden" if county==""

merge m:1 state county using "$InputPath/countyfipstool.dta"
keep if _m==3
drop _m


rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape

foreach v of varlist g20`i'_sth* g20`i'_sts* {
	tostring `v', replace
	replace `v'="" if `v'=="NA"
		destring `v', replace 
}
************************** 

save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl=="NA" & nsd>1

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu=="NA" & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace


// WASHINGTON
local i 14

import delim "$InputPath/Precinct election results/2014/wa_20`i'_heda.csv", clear 
drop state
gen state="Washington"
rename (sd ld) (sldu sldl)


tostring sldu sldl, replace

** state specific ***
do "$CodePath/elections_csld_edits/districts_WA.do" //add fips county codes
*********************

merge m:1 state county using "$InputPath/countyfipstool.dta"
keep if _m==3
drop _m

** state specific --two posts per district. Use votes for post 1 only to not double-count total votes
rename (sldu sldl g20`i'_sthp1_dv g20`i'_sthp1_rv g20`i'_sthp1_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape


foreach v of varlist g20`i'_sth* g20`i'_sts* {
	tostring `v', replace
	replace `v'="" if `v'=="NA"
		destring `v', replace 
}



save `st', replace

reshape long ld g20`i'_sth1_dv g20`i'_sth1_rv g20`i'_sth1_tv g20`i'_sth2_dv g20`i'_sth2_rv g20`i'_sth2_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl=="NA" & nsd>=1

rename (g2014_sth_rv1 g2014_sth_tv1 g2014_sth_dv1) (g2014_sth_rv g2014_sth_tv g2014_sth_dv)
collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)


append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu=="NA" & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace


// WISCONSIN  -- no state leg elections recorded in 2014

// WEST VIRGINIA
local i 14

import delim "$InputPath/Precinct election results/2014/wv_20`i'_heda.csv", clear 
drop state
gen state="West Virginia"
rename (sd ld) (sldu sldl)


tostring sldu sldl, replace


merge m:1 state county using "$InputPath/countyfipstool.dta"
keep if _m==3
drop _m


rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape

foreach v of varlist g20`i'_sth* g20`i'_sts* {
	tostring `v', replace
	replace `v'="" if `v'=="NA"
		destring `v', replace 
}

save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl=="NA" & nsd>1

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu=="NA" & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace

//WYOMING
local i 14

import delim "$InputPath/Precinct election results/2014/wy_20`i'_heda.csv", clear 
drop state
gen state="Wyoming"
rename (sd ld) (sldu sldl)


tostring sldu sldl, replace

** state specific ********
drop if precinct=="Absentee" | precinct=="Provisional"
replace county=upper(county)
****************************

merge m:1 state county using "$InputPath/countyfipstool.dta"
keep if _m==3
drop _m


rename (sldu sldl g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv) (sd1 ld1 g20`i'_sth_dv1 g20`i'_sth_rv1 g20`i'_sth_tv1 g20`i'_sts_dv1 g20`i'_sts_rv1 g20`i'_sts_tv1) //prepare for reshape

foreach v of varlist g20`i'_sth* g20`i'_sts* {
	tostring `v', replace
	replace `v'="" if `v'=="NA"
		destring `v', replace 
}

save `st', replace

reshape long ld g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv, i(year county precinct) j(nsd)
rename ld sldl
drop if sldl=="NA" & nsd>1

collapse (sum) g20`i'_sth_dv g20`i'_sth_rv g20`i'_sth_tv , by(year state fcounty sldl)

append using `csldl'
save `csldl', replace

use `st', clear

reshape long sd g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, i(year county precinct) j(nsd)
rename sd sldu
drop if sldu=="NA" & nsd>1

collapse (sum) g20`i'_sts_dv g20`i'_sts_rv g20`i'_sts_tv, by(year state fcounty sldu)

append using `csldu'
save `csldu', replace


/// export lower house data

use `csldl', clear

rename year electionyear
gen year=.
replace year=2017 

foreach i of varlist g* {
gen allmissing_`i'=0
replace allmissing_`i'=1 if `i'==.
}

collapse (sum) g* (mean) allmissing*, by(year state fcounty sldl)

foreach i of varlist g* {
replace `i'=. if allmissing_`i'==1
}

drop allmissing*

gen censusyear=2020
statastates , n(state)
keep if _m==3
rename state_f fstate
replace state=proper(state)

gen sldlname=sldl
do "$CodePath/elections_csld_edits/elections_add_sldlcodes.do" //replace string districts with code

drop fstate  state_a _m sldlname

//unable to merge MA and NH districts with sldl from Census redistricting post-2010 report
drop if state=="Massachusetts" | state=="New Hampshire"
drop if sldl=="NA"

destring sldl, replace
drop if sldl==. 

statastates, name(state) 
drop if _m!=3 
drop _m
replace state=proper(state)
rename state_fips fstate 

cd "$OutputPath"
save elections_csldl_2014, replace


/// export upper house data
use `csldu', clear
rename year electionyear
gen year=.
replace year=2017 



foreach i of varlist g* {
gen allmissing_`i'=1 if `i'==.
}

collapse (sum) g* (mean) allmissing*, by(year state fcounty sldu)

foreach i of varlist g* {
replace `i'=. if allmissing_`i'==1
}

drop allmissing*

gen censusyear=2020

statastates, name(state) 
drop if _m!=3 
drop _m
replace state=proper(state)
rename state_fips fstate 

gen slduname=sldu
do "$CodePath/elections_csld_edits/elections_add_slducodes.do" //replace string districts with code

drop if sldu=="NA"
drop if sldu==""

destring sldu, replace

cd "$OutputPath"
save elections_csldu_2014, replace
